<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="${model.initUpperName}Model">
    <resultMap id="${model.initUpperName}Map" type="${model.packageName}.model.${model.initUpperName}Model">
    	<#list model.fields as field>  
    		<#if field.dbName == 'id'>
    	<id property="${field.name}" column="${field.dbName}"/>	
    		<#else>
    	<result property="${field.name}" column="${field.dbName}"/>	
    		</#if>
        </#list>
    </resultMap>

    <sql id="table_name">
        ${model.dbName}
    </sql>

    <sql id="columns_all">
        id,
        <include refid="columns_exclude_id"/>
    </sql>

    <sql id="columns_exclude_id">
        <#list model.fields as field>
        	<#if field.dbName != 'id'>
        ${field.dbName}<#sep>,</#sep>
        	</#if>
        </#list>
    </sql>

    <sql id="values_exclude_id">
        <#list model.fields as field>
        	<#if field.dbName != 'id'>
        		<#if field.dbName?ends_with("time")>
		now()<#sep>,</#sep>
        		<#else>
        ${r"#{"}${field.name}${r"}"}<#sep>,</#sep>
        		</#if>      		
        	</#if>
        </#list>
    </sql>

    <sql id="criteria">
        <where>
        	<trim prefixOverrides="AND">
        	<#list model.fields as field>
        		<if test="${field.name} != null">AND ${field.dbName} = ${r"#{"}${field.name}${r"}"}</if>
        	</#list>
        	</trim>            
        </where>
    </sql>

    <insert id="create" parameterType="${model.packageName}.model.${model.initUpperName}Model" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO
        <include refid="table_name"/>
        (<include refid="columns_exclude_id"/>)
        VALUES
        (<include refid="values_exclude_id"/>)
    </insert>

    <insert id="creates" parameterType="${model.packageName}.model.${model.initUpperName}Model" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO
        <include refid="table_name"/>
        (<include refid="columns_exclude_id"/>)
        VALUES
        <foreach collection="list" item="i" index="index" separator=",">
        (
        <#list model.fields as field>
        	<#if field.dbName != 'id'>
        		<#if field.dbName?ends_with("time")>
        	now()<#sep>,</#sep>
        		<#else>
        	${r"#{i."}${field.name}${r"}"}<#sep>,</#sep>
        		</#if>      		
        	</#if>
        </#list>
        )
        </foreach>
    </insert>

    <select id="findById" parameterType="long" resultMap="${model.initUpperName}Map">
        SELECT
        <include refid="columns_all"/>
        FROM
        <include refid="table_name"/>
        WHERE id = ${r"#{id}"} LIMIT 1
    </select>

    <select id="findByIds" parameterType="list" resultMap="${model.initUpperName}Map">
        SELECT
        <include refid="columns_all"/>
        FROM
        <include refid="table_name"/>
        WHERE id IN
        <foreach item="id" collection="list" open="(" separator="," close=")">
            ${r"#{id}"}
        </foreach>
    </select>

    <update id="update" parameterType="${model.packageName}.model.${model.initUpperName}Model">
        UPDATE
        <include refid="table_name"/>
        <set>
        	<#list model.fields as field>
        		<#if field.dbName != "id">
        	<if test="${field.name} != null">${field.dbName} = ${r"#{"}${field.name}${r"}"},</if>
        		</#if>
        	</#list>            
            updated_at = now()
        </set>
        WHERE id = ${r"#{id}"}
    </update>

    <sql id="paging_criteria">
        <where>
        	<trim prefixOverrides="AND">
        	<#list model.fields as field>
        		<if test="${field.name} != null">AND ${field.dbName} = ${r"#{"}${field.name}${r"}"}</if>
        	</#list>
        	</trim>  
        </where>
    </sql>

    <select id="count" parameterType="map" resultType="long">
        SELECT COUNT(1)
        FROM
        <include refid="table_name"/>
        <include refid="paging_criteria"/>
    </select>

    <select id="paging" parameterType="map" resultMap="${model.initUpperName}Map">
        SELECT
        <include refid="columns_all"/>
        FROM
        <include refid="table_name"/>
        <include refid="paging_criteria"/>
        ORDER BY `id` DESC
        LIMIT ${r"#{offset}"}, ${r"#{limit}"}
    </select>

    <select id="list" parameterType="${model.packageName}.model.${model.initUpperName}Model" resultMap="${model.initUpperName}Map">
        SELECT
        <include refid="columns_all"/>
        FROM
        <include refid="table_name"/>
        <include refid="criteria"/>
        ORDER BY `id` DESC
    </select>
    
    <select id="queryOne" parameterType="${model.packageName}.model.${model.initUpperName}Model" resultMap="${model.initUpperName}Map">
        SELECT
        <include refid="columns_all"/>
        FROM
        <include refid="table_name"/>
        <include refid="criteria"/>
        ORDER BY `id` DESC
        LIMIT 1
    </select>

</mapper>
